import os
import sqlite3
# import mysql.connector as mysql
from .db_supplementary import (proceed_error, 
                            check_folder, 
                            proceed_insertion_type_subtype)
from config import (basic_values_notification,
                    basic_id_frequency,
                    basic_add_timings,
                    FilesUsage, 
                    DataBaseTable, 
                    TableUser,
                    TableTask,
                    TableType,
                    TableSubType,
                    TableNotification,
                    TableTaskExpired,
                    TableTaskFinished,
                    TableTaskProccessing,
                    TableTaskType,
                    TableTaskSubType,
                    TableTypeSubType,
                    TableTypeUser,
                    TableSubTypeUser,
                    TableUserSettings,
                    TableTaskNotification,
                    TableSubTypeNotification)


class DataUsage:
    """
    class which is dedicated for operating values in the databases
    """
    def __init__(self) -> None:
        self.storage_folder = os.path.join(FilesUsage.folder_current, FilesUsage.folder_storage)
        self.storage_name = os.path.join(self.storage_folder, FilesUsage.folder_name_db)
        self.connection, self.cursor = None, None
        self.produce_connection()
        
    def check_database_presence(self) -> bool:
        """
        Method which is dedicated to develop checking
        Input:  None
        Output: we developed boolean values
        """
        value_check = os.path.exists(self.storage_folder) and os.path.isdir(self.storage_folder)
        if not value_check:
            return value_check
        value_check = os.path.exists(self.storage_name) and os.path.isfile(self.storage_name)
        if not value_check:
            return value_check
        value_check = os.stat(self.storage_name).st_size > 0
        return value_check

    def produce_connection(self):
        """
        Method which is dedicated to develop connection to the databases
        Input:  None
        Output: we developed values of the connection of it
        """
        # try:
        presence = self.check_database_presence()
        if not presence:
            check_folder(self.storage_folder)
        self.connection = sqlite3.connect(self.storage_name, check_same_thread=False)
        self.cursor = self.connection.cursor()
        if not presence:
            self.produce_database_insertion_basics()
        # except Exception as e:
        #     msg = f"We found problems to the connection. Error: {e}"
        #     proceed_error(msg)

    def close_connection(self) -> None:
        """
        Method which is dedicated to make closing the connection of the database
        Input:  None
        Output: we closed the connection
        """
        self.connection.close()

    def produce_database_insertion_basics_type(self, list_type:list) -> None:
        """
        Method which is dedicated to make the insertion of the possible basic types of it
        Input:  list_type = list with all possible types
        Output: we inserted values to a database for further work
        """
        self.cursor.executemany(f"""
            INSERT INTO {DataBaseTable.table_type}(
                {TableType.id}, 
                {TableType.name}) 
            VALUES (?, ?);""", list_type)
        self.connection.commit()

    def produce_database_insertion_basics_subtype(self, list_subtype:list) -> None:
        """
        Method which is dedicated to make the insertion of the possible basic subtypes of it
        Input:  list_subtype = list of all possible subtypes
        Output: we inserted values to a database for further work
        """
        self.cursor.executemany(f"""
            INSERT INTO {DataBaseTable.table_subtype}(
                {TableSubType.id}, 
                {TableSubType.name}) 
            VALUES (?, ?);""", list_subtype)
        self.connection.commit()

    def produce_database_insertion_basics_notification(self) -> None:
        """
        Method which is dedicated to develop the notification basics to it
        Input:  None
        Output: we inserted basic values of it
        """
        self.cursor.executemany(f"""
            INSERT INTO {DataBaseTable.table_notification}(
                {TableNotification.id}, 
                {TableNotification.type}, 
                {TableNotification.description},
                {TableNotification.notice_day_one},
                {TableNotification.notice_week_one},
                {TableNotification.notice_week_two},
                {TableNotification.notice_week_three},
                {TableNotification.notice_week_four},
                {TableNotification.notice_month_one}
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
        """, basic_values_notification)
        self.connection.commit()

    def produce_database_insertion_basics_type_subtype(self, list_type_subtype:list) -> None:
        """
        Method which is dedicated to make the insertion of the possible type/subtype
        Input:  list_type_subtype = list of the types/subtypes of the default
        Output: we inserted values to a database for further work
        """
        self.cursor.executemany(f"""
            INSERT INTO {DataBaseTable.table_type_subtype}(
                {TableTypeSubType.id_type},
                {TableTypeSubType.id_subtype})
            VALUES (?, ?);""", list_type_subtype)
        self.connection.commit()

    def produce_database_insertion_basics(self) -> None:
        """
        Method which is dedicated to develop values of the
        Input:  None
        Output: we developed basic values of it
        """
        # try:
        if not self.connection:
            self.connection = sqlite3.connect(self.storage_name, check_same_thread=False)
            self.cursor = self.connection.cursor()
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_user}(
                {TableUser.id} INTEGER,
                {TableUser.name_first} TEXT,
                {TableUser.name_last} TEXT,
                {TableUser.username} TEXT,
                {TableUser.datetime} DATETIME DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY ({TableUser.id})
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_task}(
                {TableTask.id} INTEGER PRIMARY KEY,
                {TableTask.id_user} INTEGER NOT NULL,
                {TableTask.id_user_task} INTEGER NOT NULL,
                {TableTask.id_message} INTEGER NOT NULL,
                {TableTask.datetime_start} DATETIME DEFAULT CURRENT_TIMESTAMP,
                {TableTask.datetime_finish} DATETIME,
                {TableTask.description_start} TEXT,
                {TableTask.description_finish} TEXT,
                {TableTask.alarm_day} BOOLEAN,
                {TableTask.alarm_morning} BOOLEAN,
                {TableTask.alarm_evening} BOOLEAN);
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_type}(
                {TableType.id} INTEGER PRIMARY KEY AUTOINCREMENT,
                {TableType.name} TEXT
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_subtype}(
                {TableSubType.id} INTEGER PRIMARY KEY AUTOINCREMENT,
                {TableSubType.name} TEXT
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_task_finished}(
                {TableTaskFinished.id} INTEGER,
                {TableTaskFinished.id_user} INTEGER,
                {TableTaskFinished.datetime} DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY ({TableTaskFinished.id}) 
                    REFERENCES {DataBaseTable.table_task}({TableTask.id})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                FOREIGN KEY ({TableTaskFinished.id_user}) 
                    REFERENCES {DataBaseTable.table_task}({TableTask.id_user})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                PRIMARY KEY ({TableTaskFinished.id}, 
                            {TableTaskFinished.id_user})
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_task_processing}(
                {TableTaskProccessing.id} INTEGER,
                {TableTaskProccessing.id_user} INTEGER,
                {TableTaskProccessing.datetime} DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY ({TableTaskProccessing.id}) 
                    REFERENCES {DataBaseTable.table_task}({TableTask.id})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                FOREIGN KEY ({TableTaskProccessing.id_user}) 
                    REFERENCES {DataBaseTable.table_task}({TableTask.id_user})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                PRIMARY KEY ({TableTaskProccessing.id}, 
                            {TableTaskProccessing.id_user})
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_task_expired}(
                {TableTaskExpired.id} INTEGER,
                {TableTaskExpired.id_user} INTEGER,
                {TableTaskExpired.datetime} DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY ({TableTaskExpired.id}) 
                    REFERENCES {DataBaseTable.table_task}({TableTask.id})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                FOREIGN KEY ({TableTaskExpired.id_user}) 
                    REFERENCES {DataBaseTable.table_task}({TableTask.id_user})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                PRIMARY KEY ({TableTaskExpired.id}, 
                            {TableTaskExpired.id_user})
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_notification}(
                {TableNotification.id} PRIMARY KEY,
                {TableNotification.type} TEXT,
                {TableNotification.description} TEXT,
                {TableNotification.notice_day_one} BOOLEAN DEFAULT FALSE,
                {TableNotification.notice_month_one} BOOLEAN DEFAULT FALSE,
                {TableNotification.notice_week_one} BOOLEAN DEFAULT TRUE,
                {TableNotification.notice_week_two} BOOLEAN DEFAULT FALSE,
                {TableNotification.notice_week_three} BOOLEAN DEFAULT FALSE,
                {TableNotification.notice_week_four} BOOLEAN DEFAULT FALSE
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_task_type}(
                {TableTaskType.id_task} INTEGER,
                {TableTaskType.id_type} INTEGER,
                FOREIGN KEY ({TableTaskType.id_task}) 
                    REFERENCES {DataBaseTable.table_task}({TableTask.id})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                FOREIGN KEY ({TableTaskType.id_type}) 
                    REFERENCES {DataBaseTable.table_type}({TableType.id})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                PRIMARY KEY ({TableTaskType.id_task}, 
                            {TableTaskType.id_type})
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_task_subtype}(
                {TableTaskSubType.id_task} INTEGER,
                {TableTaskSubType.id_subtype} INTEGER,
                FOREIGN KEY ({TableTaskSubType.id_task}) 
                    REFERENCES {DataBaseTable.table_task}({TableTask.id})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                FOREIGN KEY ({TableTaskSubType.id_subtype}) 
                    REFERENCES {DataBaseTable.table_subtype}({TableSubType.id})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                PRIMARY KEY ({TableTaskSubType.id_task}, 
                            {TableTaskSubType.id_subtype})
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_type_subtype}(
                {TableTypeSubType.id_type} INTEGER,
                {TableTypeSubType.id_subtype} INTEGER,
                FOREIGN KEY ({TableTypeSubType.id_type}) 
                    REFERENCES {DataBaseTable.table_type}({TableType.id})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                FOREIGN KEY ({TableTypeSubType.id_subtype}) 
                    REFERENCES {DataBaseTable.table_subtype}({TableSubType.id})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                PRIMARY KEY ({TableTypeSubType.id_type}, 
                            {TableTypeSubType.id_subtype})
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_task_notification}(
                {TableTaskNotification.id_task} INTEGER,
                {TableTaskNotification.id_notification} INTEGER,
                FOREIGN KEY ({TableTaskNotification.id_task}) 
                    REFERENCES {DataBaseTable.table_task}({TableTask.id})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                FOREIGN KEY ({TableTaskNotification.id_notification}) 
                    REFERENCES {DataBaseTable.table_notification}({TableNotification.id})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                PRIMARY KEY ({TableTaskNotification.id_task}, 
                            {TableTaskNotification.id_notification})
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_subtype_notification}(
                {TableSubTypeNotification.id_subtype} INTEGER,
                {TableSubTypeNotification.id_notification} INTEGER,
                FOREIGN KEY ({TableSubTypeNotification.id_subtype}) 
                    REFERENCES {DataBaseTable.table_subtype}({TableSubType.id})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                FOREIGN KEY ({TableSubTypeNotification.id_notification}) 
                    REFERENCES {DataBaseTable.table_notification}({TableNotification.id})
                    ON DELETE CASCADE
                    ON UPDATE NO ACTION,
                PRIMARY KEY ({TableSubTypeNotification.id_subtype}, 
                            {TableSubTypeNotification.id_notification})
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_type_user}(
                {TableTypeUser.id_type} INTEGER,
                {TableTypeUser.id_user} INTEGER,
            FOREIGN KEY ({TableTypeUser.id_type})
                REFERENCES {DataBaseTable.table_type}({TableType.id})
                ON DELETE CASCADE
                ON UPDATE NO ACTION,
            FOREIGN KEY ({TableTypeUser.id_user})
                REFERENCES {DataBaseTable.table_user}({TableUser.id})
                ON DELETE CASCADE
                ON UPDATE NO ACTION,
            PRIMARY KEY ({TableTypeUser.id_type}, 
                        {TableTypeUser.id_user})
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_subtype_user}(
                {TableSubTypeUser.id_subtype} INTEGER,
                {TableSubTypeUser.id_user} INTEGER,
            FOREIGN KEY ({TableSubTypeUser.id_subtype})
                REFERENCES {DataBaseTable.table_subtype}({TableSubType.id})
                ON DELETE CASCADE
                ON UPDATE NO ACTION,
            FOREIGN KEY ({TableSubTypeUser.id_user})
                REFERENCES {DataBaseTable.table_user}({TableUser.id})
                ON DELETE CASCADE
                ON UPDATE NO ACTION,
            PRIMARY KEY ({TableSubTypeUser.id_subtype}, 
                        {TableSubTypeUser.id_user})
            );
        """)
        self.cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {DataBaseTable.table_user_settings}(
                {TableUserSettings.id_user} INTEGER,
                {TableUserSettings.default_by_types} BOOLEAN DEFAULT FALSE,
                {TableUserSettings.default_by_subtypes} BOOLEAN DEFAULT FALSE,
                {TableUserSettings.default_by_tasks} BOOLEAN DEFAULT FALSE,
                {TableUserSettings.default_change_morning} BOOLEAN DEFAULT TRUE,
                {TableUserSettings.default_change_day} BOOLEAN DEFAULT TRUE,
                {TableUserSettings.default_change_evening} BOOLEAN DEFAULT TRUE,
                {TableUserSettings.default_frequency} INTEGER DEFAULT {basic_id_frequency},
                {TableUserSettings.show_processing} BOOLEAN DEFAULT TRUE,
                {TableUserSettings.show_expired} BOOLEAN DEFAULT TRUE,
                {TableUserSettings.show_finished} BOOLEAN DEFAULT TRUE,
                {TableUserSettings.add_timing} INTEGER DEFAULT {basic_add_timings[3]},
            FOREIGN KEY ({TableUserSettings.id_user})
                REFERENCES {DataBaseTable.table_task}({TableTask.id_user})
                ON DELETE CASCADE
                ON UPDATE NO ACTION,
            FOREIGN KEY ({TableUserSettings.default_frequency})
                REFERENCES {DataBaseTable.table_notification}({TableNotification.id})
                ON DELETE CASCADE
                ON UPDATE NO ACTION,
            PRIMARY KEY ({TableUserSettings.id_user})
            );
        """)
        self.connection.commit()
        list_type, list_subtype, list_type_subtype = proceed_insertion_type_subtype()
        
        self.produce_database_insertion_basics_type(list_type)
        self.produce_database_insertion_basics_subtype(list_subtype)
        self.produce_database_insertion_basics_type_subtype(list_type_subtype)
        self.produce_database_insertion_basics_notification()
        # except Exception as e:
        #     msg = f"We faced problems with the basic creation of the basic. Error: {e}"
        #     print(msg)
        #     print('@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@')
        #     proceed_error(msg)

    